package com.netas.kekmanager.dao.impl;

import com.netas.kekmanager.dao.CourseRelationDao;
import com.netas.kekmanager.dao.model.CourseRelation;
import com.netas.kekmanager.dao.model.CourseRelationPk;
import com.netas.kekmanager.dao.exceptions.CourseRelationDaoException;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Transactional;

public class CourseRelationDaoImpl extends AbstractDAO implements ParameterizedRowMapper<CourseRelation>, CourseRelationDao
{
	protected SimpleJdbcTemplate jdbcTemplate;

	protected DataSource dataSource;

	/**
	 * Method 'setDataSource'
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return CourseRelationPk
	 */
	@Transactional
	public CourseRelationPk insert(CourseRelation dto)
	{
                jdbcTemplate.update("INSERT INTO " + getTableName() + " ( id, course_id, instructor_id, institution_id ) VALUES ( ?, ?, ?, ? )",dto.getId(),dto.getCourseId(),dto.getInstructorId(),dto.getInstitutionId());
		CourseRelationPk pk = new CourseRelationPk();
		pk.setId( jdbcTemplate.queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the course_relation table.
	 */
	@Transactional
	public void update(CourseRelationPk pk, CourseRelation dto) throws CourseRelationDaoException
	{
		jdbcTemplate.update("UPDATE " + getTableName() + " SET id = ?, course_id = ?, instructor_id = ?, institution_id = ? WHERE id = ?",dto.getId(),dto.getCourseId(),dto.getInstructorId(),dto.getInstitutionId(),pk.getId());
	}

	/** 
	 * Deletes a single row in the course_relation table.
	 */
	@Transactional
	public void delete(CourseRelationPk pk) throws CourseRelationDaoException
	{
		jdbcTemplate.update("DELETE FROM " + getTableName() + " WHERE id = ?",pk.getId());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return CourseRelation
	 */
	public CourseRelation mapRow(ResultSet rs, int row) throws SQLException
	{
		CourseRelation dto = new CourseRelation();
		dto.setId( rs.getInt( 1 ) );
		dto.setCourseId( rs.getInt( 2 ) );
		dto.setInstructorId( rs.getInt( 3 ) );
		dto.setInstitutionId( rs.getInt( 4 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "spring2013g3.course_relation";
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'id = :id'.
	 */
	@Transactional
	public CourseRelation findByPrimaryKey(int id) throws CourseRelationDaoException
	{
		try {
			List<CourseRelation> list = jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE id = ?", this,id);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria ''.
	 */
	@Transactional
	public List<CourseRelation> findAll() throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " ORDER BY id", this);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'instructor_id = :instructorId'.
	 */
	@Transactional
	public List<CourseRelation> findByWebResource(int instructorId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE instructor_id = ?", this,instructorId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'course_id = :courseId'.
	 */
	@Transactional
	public List<CourseRelation> findByWebResource2(int courseId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE course_id = ?", this,courseId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'institution_id = :institutionId'.
	 */
	@Transactional
	public List<CourseRelation> findByWebResource3(int institutionId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE institution_id = ?", this,institutionId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'id = :id'.
	 */
	@Transactional
	public List<CourseRelation> findWhereIdEquals(int id) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE id = ? ORDER BY id", this,id);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'course_id = :courseId'.
	 */
	@Transactional
	public List<CourseRelation> findWhereCourseIdEquals(int courseId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE course_id = ? ORDER BY course_id", this,courseId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'instructor_id = :instructorId'.
	 */
	@Transactional
	public List<CourseRelation> findWhereInstructorIdEquals(int instructorId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE instructor_id = ? ORDER BY instructor_id", this,instructorId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the course_relation table that match the criteria 'institution_id = :institutionId'.
	 */
	@Transactional
	public List<CourseRelation> findWhereInstitutionIdEquals(int institutionId) throws CourseRelationDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, course_id, instructor_id, institution_id FROM " + getTableName() + " WHERE institution_id = ? ORDER BY institution_id", this,institutionId);
		}
		catch (Exception e) {
			throw new CourseRelationDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the course_relation table that matches the specified primary-key value.
	 */
	public CourseRelation findByPrimaryKey(CourseRelationPk pk) throws CourseRelationDaoException
	{
		return findByPrimaryKey( pk.getId() );
	}

}
